package com.thirdgroup.dao.impl;

import com.thirdgroup.dao.BillingDao;
import com.thirdgroup.dao.base.BaseDAO;
import com.thirdgroup.pojo.vo.BillingInfoVO;

import java.util.List;

public class  BillingDaoImpl extends BaseDAO<BillingInfoVO> implements BillingDao {
    @Override
    public List<BillingInfoVO> selectBycustomerID(String customerID) {
        String sql="SELECT u.userPhone,b.billID, b.callDuration,b.phoneCharge, b.createTime ,cf.calledPrice ,co.calledOperatorPhone\n" +
                "FROM user_info u \n" +
                "LEFT JOIN bill_info b ON u.userPhone = b.callingPhone \n" +
                "LEFT JOIN called_operator_info co on b.calledPhone=co.calledOperatorPhone\n" +
                "left JOIN call_fees_info cf on co.calledOperatorID=cf.calledOperatorName\n" +
                "WHERE u.customerID = ?";
        Object[] params={customerID};
        return super.findAll(sql,params);
    }

    @Override
    public List<BillingInfoVO> selectBycustomerID(String customerID,String date) {
        String sql="   SELECT u.userPhone,b.billID, b.callDuration,b.phoneCharge, b.createTime ,cf.calledPrice ,co.calledOperatorPhone\n" +
                "    FROM user_info u \n" +
                "    LEFT JOIN bill_info b ON u.userPhone = b.callingPhone \n" +
                "\t\tLEFT JOIN called_operator_info co on b.calledPhone=co.calledOperatorPhone\n" +
                "\t\tleft JOIN call_fees_info cf on co.calledOperatorID=cf.calledOperatorName\n" +
                "    WHERE u.customerID = ? ";
        Object[] params={customerID,date};
        return super.findAll(sql,params);
    }
    @Override
    public Double SumBycustomerID(String customerID, String date) {
        String sql=" SELECT SUM(total.phoneCharge) sum\n" +
                "FROM (\n" +
                "    SELECT u.userPhone,b.billID, b.phoneCharge, b.createTime\n" +
                "    FROM user_info u \n" +
                "    LEFT JOIN bill_info b ON u.userPhone = b.callingPhone \n" +
                "    WHERE u.customerID = ? \n" +
                "    AND b.createTime >= DATE_SUB(?, INTERVAL 1 MONTH)\n" +
                ") AS total;";
        Object[] params={customerID,date};
        return (Double) super.getValue(sql,params);
    }

    @Override
    public Double SumBycustomerID(String customerID) {
        String sql=" SELECT SUM(total.phoneCharge) sum\n" +
                "FROM (\n" +
                "    SELECT u.userPhone,b.billID, b.phoneCharge, b.createTime\n" +
                "    FROM user_info u \n" +
                "    LEFT JOIN bill_info b ON u.userPhone = b.callingPhone \n" +
                "    WHERE u.customerID = ? \n" +
                ") AS total;";
        Object[] params={customerID};
        return (Double) super.getValue(sql,params);
    }

    @Override
    public Double Sum() {
        String sql=" SELECT SUM(total.phoneCharge) sum\n" +
                "FROM (\n" +
                "    SELECT u.userPhone,b.billID, b.phoneCharge, b.createTime\n" +
                "    FROM user_info u \n" +
                "    LEFT JOIN bill_info b ON u.userPhone = b.callingPhone \n" +
                ") AS total;";
        return (Double) super.getValue(sql,null);
    }

}
